package controllers.dbWrapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.GregorianCalendar;

import controllers.ExceptionsTypes;

import event.Event;
import event.Event.EventType;
import event.Event.PreparationType;
import event.Event.ServiceType;

public class EventWrapper {
	
	/*
	 * -------------------------for Event table-----------------------
	 */
	
	public static Collection<Event> getAllEvent(boolean closeConnection) throws Exception{
		Collection<Event> events = new ArrayList<Event>();
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".event ";
    	Statement stmt = DBWrapper.getConnection().createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
        	String eventType = rs.getString("event_type");
        	EventType et = EventType.valueOf(eventType);
        	String preparationType = rs.getString("preparation_type");
        	PreparationType pt = PreparationType.valueOf(preparationType);
        	String serviceType = rs.getString("service_type");
        	ServiceType st = ServiceType.valueOf(serviceType);
        	long idEvent = rs.getInt("id_event");
        	Calendar[] eventDates = EventWrapper.getEventDate(idEvent, false);
        	events.add(new Event(idEvent, 
        			rs.getString("event_name"), 
        			et, 
        			pt, 
        			st, 
        			eventDates[0], 
        			eventDates[1], 
        			rs.getString("event_location"), 
        			rs.getInt("number_of_guests")));
        }
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	    return events;
	}
	
	public static Event getEvent(long idEvent, boolean closeConnection) throws Exception{
		Event e = null;
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".event " +
	    		"WHERE id_event = "+idEvent;
    	Statement stmt = DBWrapper.getConnection().createStatement();
        ResultSet rs = stmt.executeQuery(query);
        if(rs.next()){
        	String eventType = rs.getString("event_type");
        	EventType et = EventType.valueOf(eventType);
        	String preparationType = rs.getString("preparation_type");
        	PreparationType pt = PreparationType.valueOf(preparationType);
        	String serviceType = rs.getString("service_type");
        	ServiceType st = ServiceType.valueOf(serviceType);
        	String place = rs.getString("event_location");
        	int guestNumber = rs.getInt("number_of_guests");
        	Calendar[] eventDates = EventWrapper.getEventDate(idEvent, false);
        	e = new Event(idEvent, 
        			rs.getString("event_name"), 
        			et, 
        			pt, 
        			st, 
        			eventDates[0], 
        			eventDates[1], 
        			place, 
        			guestNumber);
        }
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	    if(e == null)
	    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return e;
	} 
	
	private static long getEvent(String nameEvent, String location, int guestNumber, boolean closeConnection) throws Exception{
		int idEvent = 0;
	    String query = "SELECT id_event "  +
	    		"FROM "+ DBWrapper.getDbname() +".event " +
	    		"WHERE event_name='" + nameEvent + "' AND event_location='" + location + "' AND number_of_guests=" + guestNumber;
    	Statement stmt = DBWrapper.getConnection().createStatement();
        ResultSet rs = stmt.executeQuery(query);
        if(rs.next()){
        	idEvent = rs.getInt("id_event");
        }
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	    if(idEvent == 0)
	    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return idEvent;
	}
	
	/**
	 * getEventsBetweenDate(null, null) equals to getAllEvent(), getEventsBetweenDate(date1, date1) to search in a particular date
	 * @param afterThisDate filter events that starts after this date (null if no filter)
	 * @param beforeThisDate filter events that starts before this date (null if no filter)
	 * @return all events that starts in the range of dates
	 * @throws Exception 
	 */
	public static Collection<Event> getEventsBetweenDate(Calendar afterThisDate, Calendar beforeThisDate, boolean closeConnection) throws Exception{
		Collection<Event> events = new ArrayList<Event>();
		String query = "SELECT id_event "  +
				"FROM "+ DBWrapper.getDbname() +".schedule ";
		if(afterThisDate != null){
			if(beforeThisDate != null)
				query += "WHERE event_start_date BETWEEN "+ afterThisDate.getTimeInMillis() +" AND "+ beforeThisDate.getTimeInMillis();
			else
				query += "WHERE event_start_date >= "+ afterThisDate.getTimeInMillis();
		}else{
			if(beforeThisDate != null)
				query += "WHERE event_start_date <= "+ beforeThisDate.getTimeInMillis();
			else
				return EventWrapper.getAllEvent(closeConnection);
		}
	    try {
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        while (rs.next()) {
	        	events.add(EventWrapper.getEvent(rs.getInt("id_event"), false));
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    } catch (SQLException e ){
	    	throw new Exception("CONNECTION_ERROR");
	    }
	    return events;
	}
	
	public static void insertEvent(boolean closeConnection, Event ... events) throws Exception{
		Statement stmt = DBWrapper.getConnection().createStatement();
		for(int i = 0 ; i<events.length; i++){
			Event e = events[i];
			if(EventWrapper.isAvailable(e.getBeginDate(), false)){
		        String query = "INSERT INTO "+ DBWrapper.getDbname() +".event" +
	        	"(event_name, event_type, preparation_type, service_type, event_location, number_of_guests) " +
	            "VALUES('" + e.getEventName() +"', '" + 
	        	e.getEventType().name() +"', '" + 
	            e.getPreparationType().name() + "', '" +
	            e.getServiceType().name() + "', '" +
	        	e.getPlace() +"', " + 
	            e.getGuestsNumber() + ")";
				int rows = stmt.executeUpdate(query);
		        if(rows == 0)
		    	    throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
		       e.setId(getEvent(e.getEventName(), e.getPlace(), e.getGuestsNumber(), false));
			   EventWrapper.insertEventDate(false, e.getId(), e.getBeginDate(), e.getEndDate());

			}else
				throw new Exception(ExceptionsTypes.UNAVAILABLE_DATE.name());
		}
	    stmt.close();
	    if(closeConnection)
	    	DBWrapper.getConnection().close();
	}
	
	public static void deleteEvent(boolean closeConnection, Event event) throws Exception{
	    String query = "DELETE "  +
	    		"FROM "+ DBWrapper.getDbname() +".event " +
	    		"WHERE id_event = "+event.getId();
    	Statement stmt = DBWrapper.getConnection().createStatement();
        int rows = stmt.executeUpdate(query);
        if(rows>1)
        	throw new SQLException();
        else if(rows == 0)
    	    throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
        EventWrapper.deleteEventDate(false, event.getId());
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	}
	
	/*
	 * -------------------------for Schedule table-----------------------
	 */

	/**
	 * 
	 * @param afterThisDate 
	 * @param closeConnection
	 * @return collection of unique dates (days) in which there is an event
	 * @throws Exception
	 */
	public static Collection<Calendar> getAllEventsDates(Calendar afterThisDate, boolean closeConnection) throws Exception{
		Collection<Calendar> dates = new ArrayList<Calendar>();
		String query = "SELECT * "  +
				"FROM "+ DBWrapper.getDbname() +".schedule ";
		if(afterThisDate != null)
			query += "WHERE event_start_date >= "+ afterThisDate.getTimeInMillis();
    	Statement stmt = DBWrapper.getConnection().createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
        	Calendar startDate = new GregorianCalendar();
        	startDate.setTimeInMillis(rs.getLong("event_start_date"));
        	if(!dates.contains(startDate))
        		dates.add(startDate);
        	
        	Long endMillis = rs.getLong("event_end_date");
        	if(endMillis != null){
        		Calendar endDate = new GregorianCalendar();
        		endDate.setTimeInMillis(endMillis);
        		while(!startDate.equals(endDate)){
            		startDate.add(Calendar.DAY_OF_MONTH, 1);
            		if(!dates.contains(startDate))
            			dates.add(startDate);
            	}
        	}
        }
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	    return dates;
	}
	
	public static boolean isAvailable(Calendar date, boolean closeConnection) throws Exception{
		int times = 0;
		String query = "SELECT * "  +
				"FROM "+ DBWrapper.getDbname() +".schedule "+
				"WHERE (event_end_date IS NULL AND  event_start_date = " + date.getTimeInMillis()
				+ ") OR " +
				"(event_start_date <= " + date.getTimeInMillis() + " AND event_end_date >= " + date.getTimeInMillis()+")";
    	Statement stmt = DBWrapper.getConnection().createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
        	times++;
        }
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	    return times < MAX_EVENT_A_DAY;
	}
	
	/**
	 * 
	 * @param idEvent
	 * @param closeConnection
	 * @return in first position there is the start date, in the second position the end date
	 * @throws Exception
	 */
	public static Calendar[] getEventDate(long idEvent, boolean closeConnection) throws Exception{
		Calendar[] dates = new Calendar[2];
		Calendar startDate = new GregorianCalendar();
		Calendar endDate = new GregorianCalendar();
		String query = "SELECT event_start_date, event_end_date "  +
				"FROM "+ DBWrapper.getDbname() +".schedule " +
				"WHERE id_event = "+ idEvent;
    	Statement stmt = DBWrapper.getConnection().createStatement();
        ResultSet rs = stmt.executeQuery(query);
        if(rs.next()){
        	startDate.setTimeInMillis(rs.getLong("event_start_date"));
        	endDate.setTimeInMillis(rs.getLong("event_end_date"));
        	dates[0] = startDate;
        	dates[1] = endDate;
        }else
        	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	    return dates;
	}
	
	private static void insertEventDate(boolean closeConnection, long idEvent, Calendar startDate, Calendar endDate) throws Exception{

		Statement stmt = DBWrapper.getConnection().createStatement();
		int rows = stmt.executeUpdate(
		       	"INSERT INTO "+ DBWrapper.getDbname() +".schedule " +
		        "VALUES(" + idEvent + ", " +
		       	startDate.getTimeInMillis() + ", " +
		        endDate.getTimeInMillis() + ")");
		if(rows == 0)
		   	throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
	    stmt.close();
	    if(closeConnection)
	       	DBWrapper.getConnection().close();
	}
	

	private static void deleteEventDate(boolean closeConnection, long idEvent) throws Exception{
	    String query = "DELETE "  +
	    		"FROM "+ DBWrapper.getDbname() +".schedule " +
	    		"WHERE id_event = '"+idEvent+"'";
    	Statement stmt = DBWrapper.getConnection().createStatement();
        int rows = stmt.executeUpdate(query);
        if(rows>1)
        	throw new SQLException();
        else if(rows == 0)
	    	    throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	}
	
	private static final int MAX_EVENT_A_DAY = 2;
}
